96

Beginner’s Guide to Code Algorithms

96

The “call getFolderNames” statement reads each file and copies each worksheet in

each workbook over to the target workbook.

The subroutines called are here:

PickFolder

  Sub PickFolder(Folder)

  Dim fldr As FileDialog

  Dim sItem As String

  Set fldr =​ Application.FileDialog(msoFileDialogFolderPicker)

  With fldr

    .Title =​ “Please Select a Folder by clicking (mandatory)”

    .AllowMultiSelect =​ False

    .InitialFileName =​ Application.DefaultFilePath

    If .Show <> -​1 Then GoTo NextCode

    sItem =​ .SelectedItems(1)

  End With

NextCode:

  If (InStr(1, sItem, “\”) < Len(sItem)) Then

    Folder =​ sItem & “\”

  Else

    Folder =​ sItem

  End If

  Set fldr =​ Nothing

End Sub

GetFolderNames Initialize

Sub GetFolderNames(Folder)

  Dim FolderNames() As String

  Dim SubFolders As Variant

  Dim FileItem As Object

  Dim SourceFolder As Object

  FolderNameIndex =​ 0

  Set fs =​ CreateObject(“Scripting.FileSystemObject”)

  Set SourceFolder =​ fs.GetFolder(Folder)

  For Each FileItem In SourceFolder.Files

      On Error GoTo errorresume

      A =​ FileItem.Name

      Call PopulateRow(ii, A, Folder)

      ii =​ ii +​ 1

      Application.StatusBar =​ “ Number of files “ & (ii) & “ complete “

  Next

  On Error GoTo errorresume

  Exit Sub

errorresume:

    MsgBox (“Folder/​File inaccessible! “ & Folder & “\” & A)

    k =​ k +​ 1

End Sub